﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using DataAccessLayer.Mapper;
using DataAccessLayer.Models.POJO;
using MySql.Data.MySqlClient;

namespace DataAccessLayer.Service
{
    public class assetLevel2Service : assetLevel2Mapper
    {
        public static MySqlConnection connection = null;

        public void create()
        {
            connection = new MySqlConnection("data source = localhost; database = dotnet; user id = root; password = 123456; pooling = true; charset = utf8; ");
        }

        public void Open()
        {
            connection.Open();
        }

        public void Close()
        {
            connection.Close();
        }

        public bool Delete(assetLevel2Peta assetLevel2)
        {
            create();
            var db = new PetaPoco.Database(connection);
            Open();
            if (db.Delete<assetLevel2Peta>("where assetLevel1Id = @0 and ownerDeptId = @1 and assetLevel2Id = @2 ", assetLevel2.assetLevel1Id, assetLevel2.ownerDeptId,assetLevel2.assetLevel2Id) > 0)
            {
                Close();
                return true;
            }
            else
            {
                Close();
                return false;
            }
        }

        public bool Insert(assetLevel2Peta assetLevel2)
        {
            try
            {
                create();
                var db = new PetaPoco.Database(connection);
                Open();
                db.Insert(assetLevel2);
                Close();
            }
            catch (Exception ex)
            {
                Close();
                return false;
            }

            return true;
        }

        public List<assetLevel2Peta> SelectAll(String assetLevel1Id, String ownerDeptId)
        {
            create();
            var db = new PetaPoco.Database(connection);
            Open();
            String sql = "select * from assetlevel2 where assetLevel1Id = '{0}' and  ownerDeptId = '{1}'";
            sql = String.Format(sql, assetLevel1Id,ownerDeptId);
            List<assetLevel2Peta> list = db.Query<assetLevel2Peta>(sql).ToList();
            Close();
            return list;
           
        }

        public assetLevel2Peta SelectSome(String assetLevel1Id, String assetLevel2Id, String ownerDeptId)
        {
            create();
            var db = new PetaPoco.Database(connection);
            Open();
            String sql = "select * from assetlevel2 where assetLevel1Id = '{0}' and  ownerDeptId = '{1}' and  assetLevel2Id = '{2}'";
            sql = String.Format(sql, assetLevel1Id, ownerDeptId,assetLevel2Id);
            List<assetLevel2Peta> list = db.Query<assetLevel2Peta>(sql).ToList();
            Close();
            if (list.Count != 0)
                return list[0];
            else return null;
        }

        public List<assetLevel2Peta> SelectSomeSelf( string ownerId)
        {
            create();
            var db = new PetaPoco.Database(connection);
            Open();
            String sql = "select * from assetlevel2 where  ownerId = '{0}'";
            sql = String.Format(sql,  ownerId);
            List<assetLevel2Peta> list = db.Query<assetLevel2Peta>(sql).ToList();
            Close();
            return list;
        }

        public bool UpdateNumber(assetLevel2Peta assetLevel2)
        {
            
            create();
            var db = new PetaPoco.Database(connection);
            Open();
            if (db.Update<assetLevel2Peta>("set  number = @0 where assetLevel1Id = @1 and assetLevel2Id = @2 and ownerDeptId = @3  ", assetLevel2.number,assetLevel2.assetLevel1Id, assetLevel2.assetLevel2Id,assetLevel2.ownerDeptId) > 0)
            {
                Close();
                return true;
            }
            else
            {
                Close();
                return false;
            }
        }
    }
}
